Postgres Setup
Part 1, Chapter 5
In this chapter, we'll configure Postgres, get it up and running in another container, and link it to the web service. We'll also set up Tortoise ORM and configure a model.
Postgres
To configure Postgres, we'll need to add a new service to the docker-compose.yml file, add the appropriate environment variables, and install asyncpg.
Add a "db" directory to "project", and add a create.sql file in that new directory:
CREATE DATABASE web_dev;
CREATE DATABASE web_test;
Next, add a Dockerfile to the same directory:
# pull official base image
FROM postgres:14-alpine
# run create.sql on init
ADD create.sql /docker-entrypoint-initdb.d
Here, we extended the official Postgres image (an Alpine-based image) by adding create.sql to the "docker-entrypoint-initdb.d" directory in the container. This file will execute on init.
Next, add a new service called web-db to docker-compose.yml:
version: '3.8'
services:
web:
build: ./project
command: uvicorn app.main:app --reload --workers 1 --host 0.0.0.0 --port 8000
volumes:
- ./project:/usr/src/app
ports:
- 8004:8000
environment:
- ENVIRONMENT=dev
- TESTING=0
- DATABASE_URL=postgres://postgres:postgres@web-db:5432/web_dev # new
- DATABASE_TEST_URL=postgres://postgres:postgres@web-db:5432/web_test # new
depends_on: # new
- web-db
# new
web-db:
build:
context: ./project/db
dockerfile: Dockerfile
expose:
- 5432
environment:
- POSTGRES_USER=postgres
- POSTGRES_PASSWORD=postgres
Once spun up, Postgres will be available on port 5432 for services running in other containers. Since the web service is dependent not only on the container being up and running but also the actual Postgres instance being up and healthy, let's add an entrypoint.sh file to the "project" directory:
#!/bin/sh
echo "Waiting for postgres..."
while ! nc -z web-db 5432; do
sleep 0.1
done
echo "PostgreSQL started"
exec "$@"
So, we referenced the Postgres container using the name of the service, web-db. The loop continues until something like Connection to web-db port 5432 [tcp/postgresql] succeeded! is returned.
Update Dockerfile to install the appropriate packages and supply an entrypoint:
# pull official base image
FROM python:3.10.1-slim-buster
# set working directory
WORKDIR /usr/src/app
# set environment variables
ENV PYTHONDONTWRITEBYTECODE 1
ENV PYTHONUNBUFFERED 1
# install system dependencies
RUN apt-get update \
&& apt-get -y install netcat gcc postgresql \
&& apt-get clean
# install python dependencies
RUN pip install --upgrade pip
COPY ./requirements.txt .
RUN pip install -r requirements.txt
# add app
COPY . .
# add entrypoint.sh
COPY ./entrypoint.sh .
RUN chmod +x /usr/src/app/entrypoint.sh
# run entrypoint.sh
ENTRYPOINT ["/usr/src/app/entrypoint.sh"]
Add asyncpg to project/requirements.txt:
asyncpg==0.25.0
Next, update config.py to read the DATABASE_URL and assign the value to database_url:
# project/app/config.py
import logging
import os
from functools import lru_cache
from pydantic import BaseSettings, AnyUrl
log = logging.getLogger("uvicorn")
class Settings(BaseSettings):
environment: str = os.getenv("ENVIRONMENT", "dev")
testing: bool = os.getenv("TESTING", 0)
database_url: AnyUrl = os.environ.get("DATABASE_URL")
@lru_cache()
def get_settings() -> BaseSettings:
log.info("Loading config settings from the environment...")
return Settings()
Take note of the AnyUrl validator.
Build the new image and spin up the two containers:
$ chmod +x project/entrypoint.sh
$ docker-compose up -d --build
Once done, check the logs for the web service:
$ docker-compose logs web
You should see:
web_1 | Waiting for postgres...
web_1 | PostgreSQL started
web_1 | INFO: Will watch for changes in these directories: ['/usr/src/app']
web_1 | INFO: Uvicorn running on http://0.0.0.0:8000 (Press CTRL+C to quit)
web_1 | INFO: Started reloader process [1] using statreload
web_1 | INFO: Started server process [37]
web_1 | INFO: Waiting for application startup.
web_1 | INFO: Application startup complete.
Depending on your environment, you may need to chmod 755 or 777 instead of +x. If you still get a "permission denied", review the docker entrypoint running bash script gets "permission denied" Stack Overflow question.
Want to access the database via psql?
$ docker-compose exec web-db psql -U postgres
Then, you can connect to the database:
postgres=# \c web_dev
postgres=# \q
Tortoise ORM
To simplify interactions with the database, we'll using an async ORM called Tortoise.
Add the dependency to the requirements.txt file:
tortoise-orm==0.17.8
Next, create a new folder called "models" within "project/app". Add two files to "models": __init__.py and tortoise.py.
In tortoise.py, add:
# project/app/models/tortoise.py
from tortoise import fields, models
class TextSummary(models.Model):
url = fields.TextField()
summary = fields.TextField()
created_at = fields.DatetimeField(auto_now_add=True)
def __str__(self):
return self.url
Here, we defined a new database model called TextSummary.
Add the register_tortoise helper to main.py to set up Tortoise on startup and clean up on teardown:
# project/app/main.py
import os
from fastapi import FastAPI, Depends
from tortoise.contrib.fastapi import register_tortoise
from app.config import get_settings, Settings
app = FastAPI()
register_tortoise(
app,
db_url=os.environ.get("DATABASE_URL"),
modules={"models": ["app.models.tortoise"]},
generate_schemas=True,
add_exception_handlers=True,
)
@app.get("/ping")
async def pong(settings: Settings = Depends(get_settings)):
return {
"ping": "pong!",
"environment": settings.environment,
"testing": settings.testing
}
Sanity check:
$ docker-compose up -d --build
Ensure the textsummary table was created:
$ docker-compose exec web-db psql -U postgres
psql (14.1)
Type "help" for help.
postgres=# \c web_dev
You are now connected to database "web_dev" as user "postgres".
web_dev=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------------+-------+----------
public | textsummary | table | postgres
(1 row)
web_dev=# \q
http://localhost:8004/ping should still work as well:
{
"ping": "pong!",
"environment": "dev",
"testing": false
}
You should now have:
├── .gitignore
├── docker-compose.yml
└── project
├── .dockerignore
├── Dockerfile
├── app
│ ├── __init__.py
│ ├── config.py
│ ├── main.py
│ └── models
│ ├── __init__.py
│ └── tortoise.py
├── db
│ ├── Dockerfile
│ └── create.sql
├── entrypoint.sh
└── requirements.txt
Migrations
Tortoise supports database migrations via Aerich. Let's take a few steps back and configure it.
First, bring down the containers and volumes to destroy the current database table since we want Aerich to manage the schema:
$ docker-compose down -v
Next, update the register_tortoise helper in project/app/main.py so that the schemas are not automatically generated:
register_tortoise(
app,
db_url=os.environ.get("DATABASE_URL"),
modules={"models": ["app.models.tortoise"]},
generate_schemas=False, # updated
add_exception_handlers=True,
)
Spin the containers and volumes back up:
$ docker-compose up -d --build
Ensure the textsummary table was NOT created:
$ docker-compose exec web-db psql -U postgres
psql (14.1)
Type "help" for help.
postgres=# \c web_dev
You are now connected to database "web_dev" as user "postgres".
web_dev=# \dt
Did not find any relations.
web_dev=# \q
Add Aerich to the requirements file:
aerich==0.6.1
Then, update the containers:
$ docker-compose up -d --build
Aerich requires the Tortoise config, so let's add that to a new file called project/app/db.py:
# project/app/db.py
import os
TORTOISE_ORM = {
"connections": {"default": os.environ.get("DATABASE_URL")},
"apps": {
"models": {
"models": ["app.models.tortoise", "aerich.models"],
"default_connection": "default",
},
},
}
Now we're ready to start using Aerich.
Init:
$ docker-compose exec web aerich init -t app.db.TORTOISE_ORM
This will create a config file called project/pyproject.toml:
[tool.aerich]
tortoise_orm = "app.db.TORTOISE_ORM"
location = "./migrations"
src_folder = "./."
Create the first migration:
$ docker-compose exec web aerich init-db
Success create app migrate location migrations/models
Success generate schema for app "models"
You should see the following migration inside a new file within "migrations/models":
-- upgrade --
CREATE TABLE IF NOT EXISTS "textsummary" (
"id" SERIAL NOT NULL PRIMARY KEY,
"url" TEXT NOT NULL,
"summary" TEXT NOT NULL,
"created_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS "aerich" (
"id" SERIAL NOT NULL PRIMARY KEY,
"version" VARCHAR(255) NOT NULL,
"app" VARCHAR(20) NOT NULL,
"content" JSONB NOT NULL
);
That's it! You should now be able to see the two tables:
$ docker-compose exec web-db psql -U postgres
psql (14.1)
Type "help" for help.
postgres=# \c web_dev
You are now connected to database "web_dev" as user "postgres".
web_dev=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------------+-------+----------
public | aerich | table | postgres
public | textsummary | table | postgres
(2 rows)
web_dev=# \q
Be sure to review the documentation to learn more about the available commands. Take note of the downgrade, history, and upgrade commands.
✓ Mark as Completed